<html>
<head>
	<title>Programming with SQL Relay using ODBC</title>
	<link rel="stylesheet" href="../css/styles.css">
</head>
<body>
<h1>Programming with SQL Relay using ODBC</h1>

<p>The SQL Relay distribution provides an ODBC driver, enabling applications which use ODBC to access SQL Relay.</p>

<h2>Unix/Linux</h2>

<p>On Unix or Linux platforms, using unixODBC or iODBC, you can create an instance for the SQL Relay driver by adding an entry like the following to your ODBC instances file (usually /etc/odbcinst.ini)</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt><font color="#990000">[</font>SQLRelay<font color="#990000">]</font>
Description<font color="#990000">=</font>ODBC <b><font color="#0000FF">for</font></b> SQL Relay
Driver<font color="#990000">=/</font>usr<font color="#990000">/</font>local<font color="#990000">/</font>firstworks<font color="#990000">/</font>lib<font color="#990000">/</font>libsqlrodbc<font color="#990000">.</font>so
FileUsage<font color="#990000">=</font><font color="#993399">0</font></tt></pre>

</blockquote>
<p>Note that the Driver attribute must bet set to the full pathname of the libsqlrodbc.so driver.  Note also that on Mac OS X you'll need to replace the <b>.so</b> suffix with <b>.dylib</b></p>

<p>Once the instance is defined you can add a DSN for SQL Relay by adding an entry like the following to your ODBC INI file (usually /etc/odbc.ini)</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt><font color="#990000">[</font>sqlrtest<font color="#990000">]</font>
Description<font color="#990000">=</font>Connection to SQL Relay
Driver<font color="#990000">=</font>SQLRelay
Server<font color="#990000">=</font>sqlrserver
Port<font color="#990000">=</font><font color="#993399">9000</font>
Socket<font color="#990000">=/</font>tmp<font color="#990000">/</font>test<font color="#990000">.</font>socket
User<font color="#990000">=</font>testuser
Password<font color="#990000">=</font>testpass</tt></pre>

</blockquote>
<p>Here the Driver attribute refers to the instance in odbcinst.ini.</p>

<p>Alternatively, you could create a combined instance/DSN by adding an entry like the following to your ODBC INI file (usually /etc/odbc.ini)</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt><font color="#990000">[</font>sqlrtest<font color="#990000">]</font>
Description<font color="#990000">=</font>Connection to SQL Relay
Driver<font color="#990000">=/</font>usr<font color="#990000">/</font>local<font color="#990000">/</font>firstworks<font color="#990000">/</font>lib<font color="#990000">/</font>libsqlrodbc<font color="#990000">.</font>so
FileUsage<font color="#990000">=</font><font color="#993399">0</font>
Server<font color="#990000">=</font>sqlrserver
Port<font color="#990000">=</font><font color="#993399">9000</font>
Socket<font color="#990000">=/</font>tmp<font color="#990000">/</font>test<font color="#990000">.</font>socket
User<font color="#990000">=</font>testuser
Password<font color="#990000">=</font>testpass</tt></pre>

</blockquote>
<h2>Windows</h2>

<p>On Windows, just use the ODBC control panel to create a DSN.</p>

<h2>Attributes</h2>

<p>The following attributes can be configured:</p>

<ul>
  <li><b>Description</b> - A user-friendly description of this configuration.</li>
  <li><b>Driver</b></li>
  <ul>
    <li>In the odbc.ini file - The name of the section of the odbcinst.ini file that specifies which driver library to use.  If an odbc.ini file is being used but no odbcinst.ini file is being used, then this is the file name of the driver library to use.</li>
    <li>In the odbcinst.ini file - The file name of the driver library to use.</li>
    <li>This parameter is not available via the Windows control panel because the driver name is configured during installation and the parameter is hardcoded to the correct value for each instance.</li>
  </ul>

  <li><b>FileUsage</b> - Always set to 0.  0 indicates that the driver is an RDBMS driver.  1 means that each file is a table (eg. Xbase).  2 means that each file is an entire database (eg. Access).  This parameter is not available via the Windows control panel because it is configured during installation.</li>
  <li><b>Server</b> - The SQL Relay server to connect to.</li>
  <li><b>Port</b> - The port that the SQL Relay server is listening on. Either port or socket must be specified. If both are specified, socket will be preferred to port.</li>
  <li><b>Socket</b> - The socket that the SQL Relay server is listening on. Either port or socket must be specified. If both are specified, socket will be preferred to port.</li>
  <li><b>User</b> - The username to use when logging into SQL Relay.</li>
  <li><b>Password</b> - The password to use when logging into SQL Relay.</li>
  <li><b>Tries</b> - If a connection fails, it will be retried this many times.</li>
  <li><b>RetryTime</b> - If a connection fails, it will be retried on this interval (in seconds).</li>
  <li><b>Db</b> - The database to switch to when logging into SQL Relay.  May be left empty or omitted to accept the default database that SQL Relay is already connected to.</li>
  <li><b>Debug</b> -  If this is set to 1 or to a file name then debug is enabled. If set to a file name then debug will be written to the file. If set to 1 then debug will be written to standard output. Defaults to 0.</li>
  <li><b>ResultSetBufferSize</b> - Sets the number of rows to buffer at a time.  If set to 0 (the default) then the entire result set is buffered.</li>
  <ul>
    <li><b>NOTE</b>: This parameter is dangerous to set and probably shouldn't be set unless you wrote the application and know that it is safe to set.  ResultSetBufferSize basically sets SQL_ROWSET_SIZE, but apps generally assume that SQL_ROWSET_SIZE is set to 1 unless they explicitly set it to something else.  If ResultSetBufferSize is set to something other than 0 or 1, and the app assumes that SQL_ROWSET_SIZE is 1 then the app will only fetch the first row of each rowset.</li>
  </ul>

  <li><b>ColumnNameCase</b> - Indicates the case to convert column names to.  May be set to "mixed", "upper", or "lower".  Defaults to "mixed".</li>
  <li><b>DontGetColumnInfo</b> - If this is set to 1 then column info is not fetched with the result set. If set to 0 then column info is fetched. Defaults to 0.</li>
  <li><b>NullsAsNulls</b> - If this is set to 1 then NULL values are returned as NULLs. If set to 0 then NULL values are returned as empty strings. Defaults to 0.</li>
  <li><b>LazyConnect</b> - By default, the SQL Relay client does "lazy connects". I.e. the connection to the SQL Relay server isn't established until the first query or other operation is run. This optimizes availability of the connection pool but is inconstent with most other database API's. Setting this parameter to 0 causes the connection to the SQL Relay server to be established immediately and the constructor to fail, returning undef, if the connection cannot be established. Defaults to 1.</li>
</ul>

<p>The following attributes can be used to establish Kerberos or Active Directory encryption and authentication with the server:</p>

<p>See the <a href="../admin/configguide.html#krb">SQL Relay Configuration Guide</a> for more information about Kerberos and Active Directory configurations.  In particular, User and Password are not typically used when using Kerberos/AD.</p>

<ul>
  <li><b>Krb</b> - Set to <b>yes</b> to enable Kerberos/AD encryption/authentication or <b>no</b> to disable it.</li>
  <li><b>Krbservice</b> - Specifies the Kerberos service name of the SQL Relay server.  If omitted or left empty then the service name "sqlrelay" will be used. "sqlrelay" is the default service name of the SQL Relay server.  Note that on Windows platforms the service name must be fully qualified, including the host and realm name.  For example: "sqlrelay/sqlrserver.firstworks.com@AD.FIRSTWORKS.COM".</li>
  <li><b>Krbmech</b> - Specifies the Kerberos mechanism to use.  On Linux/Unix platforms, this should be a string representation of the mechnaism's OID, such as: { 1 2 840 113554 1 2 2 }.  On Windows platforms, this should be a string like: Kerberos.  If omitted or left empty then the default mechanism will be used.  Only set this if you know that you have a good reason to.</li>
  <li><b>Krbflags</b> - Specifies what Kerberos flags to use.  Multiple flags may be specified, separated by commas.  If left empty or NULL then a defalt set of flags will be used.  Only set this if you know that you have a good reason to.  Valid flags include: GSS_C_MUTUAL_FLAG, GSS_C_REPLAY_FLAG, GSS_C_SEQUENCE_FLAG, GSS_C_CONF_FLAG, and GSS_C_INTEG_FLAG.  For a full list of flags, consult the GSSAPI documentation, though note that only the previously listed flags are supported on Windows.</li>
</ul>

<p>The following attributes can be used to establish TLS/SSL encryption and authentication with the server:</p>

<p>See the <a href="../admin/configguide.html#tls">SQL Relay Configuration Guide</a> for more information about TLS/SSL configurations.</p>

<ul>
  <li><b>Tls</b> - Set to <b>yes</b> to enable TLS/SSL encryption/authentication or <b>no</b> to disable it.</li>
  <li><b>Tlsversion</b> - Specifies the TLS/SSL protocol version that the client will attempt to use.  Valid values include SSL2, SSL3, TLS1, TLS1.1, TLS1.2 or any more recent version of TLS, as supported by and enabled in the underlying TLS/SSL library.  If omittted or left empty then the highest supported version will be negotiated.</li>
  <li><b>Tlscert</b> - Specifies the file name of the certificate chain file to send to the SQL Relay server.  This is only necessary if the SQL Relay server is configured to authenticate and authorize clients by certificate.</li>
  <li><b>Tlspassword</b> - If Tlscert contains a password-protected private key, then Tlspassword may be supplied to access it.  If the private key is not password-protected, then this argument is ignored, and may be omitted or left empty.</li>
  <li><b>Tlsciphers</b> - Specifies a list of ciphers to allow.  Ciphers may be separated by spaces, commas, or colons.  If omitted or left empty then a default set is used.  For a list of valid ciphers on Linux/Unix platforms, see: man ciphers. For a list of valid ciphers on Windows platforms, see <a target="_blank" href="https://msdn.microsoft.com/en-us/library/windows/desktop/aa375549%28v=vs.85%29.aspx">this page</a>.  On Windows platforms, the ciphers (alg_id's) should omit CALG_ and may be given with underscores or dashes.  For example: 3DES_112.  Only set this if you know that you have a good reason to.</li>
  <li><b>Tlsvalidate</b> - Indicates whether to validate the SQL Relay server's certificate, and may be set to one of the following: <b>no</b> - Don't validate the server's certificate.  <b>ca</b> - Validate that the server's certificate was signed by a trusted certificate authority.  <b>ca+host</b> - Perform "ca" validation and also validate that one of the subject altenate names (or the common name if no SANs are present) in the certificate matches the host parameter.  (Falls back to "ca" validation when a unix socket is used.)  <b>ca+domain</b> - Perform "ca" validation and also validate that the domain name of one of the subject alternate names (or the common name if no SANs are present) in the certificate matches the domain name of the host parameter.  (Falls back to "ca" validation when a unix socket is used.)</li>
  <li><b>Tlsca</b> - Specifies the location of a certificate authority file to use, in addition to the system's root certificates, when validating the SQL Relay server's certificate.  This is useful if the SQL Relay server's certificate is self-signed.  On Windows, "ca" must be a file name.  On non-Windows systems, "ca" can be either a file or  directory name.  If it is a directory name, then all certificate authority files found in that directory will be used.  If it a file name, then only that file will be used.</li>
</ul>

<p>Note that the supported Tlscert and Tlsca file formats may vary between platforms.  A variety of file formats are generally supported on Linux/Unix platfoms (.pem, .pfx, etc.) but only the .pfx format is currently supported on Windows.</p>

<h2>Sample Session</h2>

<p>Now you can use the <i>isql</i> command line utility that comes with iODBC
or unixODBC to access the database through SQL Relay as follows:</p>

<blockquote>
<b>isql sqlrtest</b>
</blockquote>
<p>Here is a sample session:</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt><font color="#990000">[</font>dmuse@fedora <font color="#990000">~]</font>$ isql sqlrodbc
<font color="#990000">+---------------------------------------+</font>
<font color="#990000">|</font> Connected<font color="#990000">!</font>                            <font color="#990000">|</font>
<font color="#990000">|</font>                                       <font color="#990000">|</font>
<font color="#990000">|</font> sql<font color="#990000">-</font>statement                         <font color="#990000">|</font>
<font color="#990000">|</font> help <font color="#990000">[</font>tablename<font color="#990000">]</font>                      <font color="#990000">|</font>
<font color="#990000">|</font> quit                                  <font color="#990000">|</font>
<font color="#990000">|</font>                                       <font color="#990000">|</font>
<font color="#990000">+---------------------------------------+</font>
SQL<font color="#990000">&gt;</font> create <font color="#008080">table</font> <b><font color="#000000">test</font></b> <font color="#990000">(</font><font color="#008080">col1</font> <font color="#009900">int</font><font color="#990000">,</font> <font color="#008080">col2</font> <b><font color="#000000">varchar2</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font>
SQLRowCount returns <font color="#993399">0</font>
SQL<font color="#990000">&gt;</font> insert into <font color="#008080">test</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font><font color="#993399">1</font><font color="#990000">,</font><font color="#FF0000">'hello'</font><font color="#990000">)</font>
SQLRowCount returns <font color="#993399">1</font>
SQL<font color="#990000">&gt;</font> insert into <font color="#008080">test</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font><font color="#993399">2</font><font color="#990000">,</font><font color="#FF0000">'goodbye'</font><font color="#990000">)</font>
SQLRowCount returns <font color="#993399">1</font>
SQL<font color="#990000">&gt;</font> select <font color="#990000">*</font> from test
<font color="#990000">+-----+--------+</font>
<font color="#990000">|</font> COL1<font color="#990000">|</font> COL2   <font color="#990000">|</font>
<font color="#990000">+-----+--------+</font>
<font color="#990000">|</font> <font color="#993399">1</font>   <font color="#990000">|</font> hello  <font color="#990000">|</font>
<font color="#990000">|</font> <font color="#993399">2</font>   <font color="#990000">|</font> goodbye<font color="#990000">|</font>
<font color="#990000">+-----+--------+</font>
SQLRowCount returns <font color="#993399">0</font>
<font color="#993399">2</font> rows fetched
SQL<font color="#990000">&gt;</font> update test <font color="#008080">set</font> col2<font color="#990000">=</font><font color="#FF0000">'bye'</font> <font color="#008080">where</font> col1<font color="#990000">=</font><font color="#993399">2</font>
SQLRowCount returns <font color="#993399">1</font>
SQL<font color="#990000">&gt;</font> select <font color="#990000">*</font> from test
<font color="#990000">+-----+------+</font>
<font color="#990000">|</font> COL1<font color="#990000">|</font> COL2 <font color="#990000">|</font>
<font color="#990000">+-----+------+</font>
<font color="#990000">|</font> <font color="#993399">1</font>   <font color="#990000">|</font> hello<font color="#990000">|</font>
<font color="#990000">|</font> <font color="#993399">2</font>   <font color="#990000">|</font> bye  <font color="#990000">|</font>
<font color="#990000">+-----+------+</font>
SQLRowCount returns <font color="#993399">0</font>
<font color="#993399">2</font> rows fetched
SQL<font color="#990000">&gt;</font> <b><font color="#0000FF">delete</font></b> from test
SQLRowCount returns <font color="#993399">2</font>
SQL<font color="#990000">&gt;</font> select <font color="#990000">*</font> from test
<font color="#990000">+-----+-----+</font>
<font color="#990000">|</font> COL1<font color="#990000">|</font> COL2<font color="#990000">|</font>
<font color="#990000">+-----+-----+</font>
<font color="#990000">+-----+-----+</font>
SQLRowCount returns <font color="#993399">0</font>
SQL<font color="#990000">&gt;</font> drop table test
SQLRowCount returns <font color="#993399">0</font>
SQL<font color="#990000">&gt;</font> quit
<font color="#990000">[</font>dmuse@fedora <font color="#990000">~]</font>$ </tt></pre>

</blockquote>
<p>If you get an error like <b>ISQL ERROR: Could not SQLConnect</b> when you run isql then it's possible that it can't find your ODBC INI file.  Some versions of unixODBC, when compiled and installed with non-standard prefixes, still look for odbc.ini in /etc rather than under their prefix.  For example, I discovered that on Mac OS X, unixODBC 2.2.12 installed under /sw should look for /sw/etc/odbc.ini but instead it looks for /etc/odbc.ini, even though <i>odbcinst -j</i> looks under /sw.</p>

<p>You can work around this by setting environment variables to override whatever default search path unixODBC uses.  For example, to force it to look under /sw/etc, use:</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt><font color="#008080">export</font> ODBCINST<font color="#990000">=/</font>sw<font color="#990000">/</font>etc<font color="#990000">/</font>odbcinst<font color="#990000">.</font>ini
<font color="#008080">export</font> ODBCINI<font color="#990000">=/</font>sw<font color="#990000">/</font>etc<font color="#990000">/</font>odbc<font color="#990000">.</font>ini</tt></pre>

</blockquote>
<p>Any program that uses ODBC can be configured to use SQL Relay via ODBC by setting its DSN to the SQL Relay DSN that you defined. </p>

<p>You can also use SQL Relay with any language that supports ODBC directly or has a database abstraction layer that supports ODBC.  For example, lets say you have an existing PHP app that uses MDB2 to connect to Oracle and you want to use SQL Relay with it.  There is no PHP MDB2 driver for SQL Relay, but since MDB2 suppots ODBC, you could use:</p>

<blockquote>
<!-- Generator: GNU source-highlight 3.1.8
by Lorenzo Bettini
http://www.lorenzobettini.it
http://www.gnu.org/software/src-highlite -->
<pre><tt>PHP <font color="#990000">-&gt;</font> MDB2 <font color="#990000">-&gt;</font> ODBC <font color="#990000">-&gt;</font> <font color="#008080">SQL</font> Relay <font color="#990000">-&gt;</font> Oracle</tt></pre>

</blockquote>
<p>Similarly, you could use SQL Relay via ODBC with PHP/PDO, PHP/ADODB, Java/JDBC (<a href="jdbc.html">using the JDBC-ODBC bridge</a>), with the ODBC Adapter for Ruby on Rails, and so on.</p>

<p>Developing applications which use ODBC is beyond the scope if this document but many examples and tutorials can be found on the web.
</p>

</body>
</html>
